<?

//Initiate Authenticate
SecurityRedirect ();

//initiate Vars 
define("PRIMARY_KEY","kdcust");
$KEY[]         = array("PRIMARY_KEY"=>PRIMARY_KEY);
$FileName      = "ais_transaction_manex.php";
$FileTemplate  = "ais_transaction_manex_mmlist.html";
$PageSize      = 10 ;
$TableName     = "ais_hutang_remain";
$HTMLGridList  = "CustomerGrid";
//************ FORM ACTION **************
	if (GetParam("Action","")=="Delete") {
		DeleteRecord ("txtCheck",$PageSize);
	}

$FCode = GetParam("memorialCodeSearch","");
$FDebtur = GetParam("debtur","");
$FDate = GetParam("transDate","");


//Initiate Database Table For Search;
//******* DATABASE FORM QUERY ***********
$DBMysql = $DBConnection->GetConnection1();

GridList (GetParam("transDate",""),GetOfficeID(),GetParam("curr",""),GetParam("debtur",""));

$SQL      = "
                 SELECT $TableName.*, ais_memorial.princ, ais_memorial.vessel, ais_memorial.voyage, ais_memorial.port
                           ,ais_hutang.nobkta,ais_hutang.rate,ais_hutang.skada, ais_memorial.dk as dk, (ifnull(jumlahcurr,0)+ifnull(jumlah30,0)+ifnull(jumlah60,0)+ifnull(jumlah60g,0)) as total_hutang
                 FROM $TableName, ais_hutang
                 LEFT JOIN ais_memorial ON ais_hutang.nobkt=ais_memorial.nobkt AND ais_memorial.urut=999
                 WHERE ais_hutang.debtur='".GetParam("debtur","")."' AND                  
                 $TableName.nobkt like 'MM%' AND $TableName.nobkt=ais_hutang.nobkt 
                ";

$SQLCount = "SELECT count(*) 
                   FROM $TableName, ais_hutang
                   LEFT JOIN ais_memorial ON ais_hutang.nobkt=ais_memorial.nobkt AND ais_memorial.urut=999
                   WHERE ais_hutang.debtur='".GetParam("debtur","")."' AND                  
                   $TableName.nobkt like 'MM%' AND $TableName.nobkt=ais_hutang.nobkt 
                   ";

//******* SHOW FORM CONTENT *********
//$SQL .= "GROUP BY $TableName.nobkt ";
//$SQLCount .= " GROUP BY $TableName.nobkt ";


$PageNum = 1;

$RecCnt  = $DBConnection->GetResSQL($SQLCount,$DBMysql);

//print $SQL;

if (GetParam("PageNum","")) $PageNum  = GetParam("PageNum","");

$TBS = new clsTinyButStrong ;
$TBS->LoadTemplate($MOD_TEMPLATE_DIR."/".$FileTemplate);
$TBS->MergeBlock("bln",$KEY);

$TBS->MergeBlock("blg",$DBMysql,$SQL,$PageSize,$PageNum,$RecCnt);

$TBS->MergeNavigationBar('nv',array('size'=>10,'pos'=>'centred'),$PageNum,$RecCnt,$PageSize) ; 
$TBS->Show();

function FlushCustomer ($TableName, $tmp) {
	global $DBConnection;

	if ($tmp) $TEMPORARY = "TEMPORARY";
	$DBC = new mydb(DB_USER, DB_PASSWORD, DB_NAME2, DB_HOST);

	$SQL = "SELECT kodecustomer,nama,cr_term,kdtype FROM customer ";
	if (GetParam("kdtype","")) $SQL .= " WHERE kdtype='".GetParam("kdtype","")."'";
	$MyResult = $DBC->dbc->get_results($SQL,ARRAY_N);
	if (!$MyResult) return false;
 	$session_file = substr(md5(time()), 0, 8);
   	$session_file = "/tmp/".$session_file.".tmp";
	BufferToFile ($MyResult,$session_file);

	$DBConnection = new mydb(DB_USER, DB_PASSWORD, DB_NAME, DB_HOST);   	
        $TableNamea = "ais_cust";
        
	$SQL = "DROP TABLE IF EXISTS `$TableNamea`;";
	$DBConnection->dbc->query($SQL);

	$SQL= "CREATE $TEMPORARY TABLE `$TableNamea` (
			  `kodecustomer` varchar(20) NOT NULL default '',
			  `nama` varchar(50) default NULL,
			  `cr_term` int(4) default NULL,
			  `kdtype` varchar(20) default NULL
			  )";
	$DBConnection->dbc->query($SQL);

   	$SQL = "LOAD DATA INFILE '$session_file' INTO TABLE $TableNamea FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\n'";
	$DBConnection->dbc->query($SQL);
        $SQL = "INSERT INTO $TableNamea SELECT kdvendor,nama,top,kdtype FROM tblvendor ";
        $DBConnection->dbc->query($SQL);
        
        $SQL = "INSERT INTO $TableNamea SELECT kdother,nama,'30','' FROM tblother ";
        $DBConnection->dbc->query($SQL);        
        
	$SQL = "DROP TABLE IF EXISTS `$TableName`;";
	$DBConnection->dbc->query($SQL);

	$SQL= "CREATE $TEMPORARY TABLE `$TableName` (
			  `kodecustomer` varchar(20) NOT NULL default '',
			  `nama` varchar(50) default NULL,
			  `cr_term` int(4) default NULL,
			  `kdtype` varchar(20) default NULL
			  )";
	$DBConnection->dbc->query($SQL);
        $SQL = "INSERT INTO `$TableName` SELECT * FROM $TableNamea GROUP BY kodecustomer ";
        $DBConnection->dbc->query($SQL);
        
	unlink($session_file);
	return true;
}

function GridList ($transDate,$office,$curr,$debtur) {
		 	global $DBConnection;
			//$TEMP = "TEMPORARY";

		 	FlushCustomer ("ais_customer", 1);
			//$current_date = date("Y-m-d",mktime (0,0,0,GetParam("m",""),1,GetParam("y","")));

			list($day,$month,$year) = explode("/",$transDate);
			$current_date = "$year-$month-$day";

			$SQL = "DROP TABLE IF EXISTS `ais_hutang_terhutang`";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE `ais_hutang_terhutang` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,	
                                          `nobkta` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
					  `jumlah` varchar(32) default NULL,
					  `cr_term` tinyint(4)default NULL,
					  `aging` varchar(4)default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);
                        
			$SQL = "
					INSERT INTO ais_hutang_terhutang
					SELECT KTRASL, TGLBKT, NOBKT,NOBKTA, DEBTUR, DEBNAM, URAIAN, CURR, JUMLAH,	IFNULL(CR_TERM,0) AS TOP, 
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL ais_customer.cr_term DAY ),'0',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 30+ais_customer.cr_term DAY ),'30',
                                        IF('$current_date'<=ADDDATE( tglbkt,INTERVAL 60+ais_customer.cr_term DAY ),'60','60g')))
					FROM ais_hutang
					LEFT JOIN ais_customer ON ais_hutang.DEBTUR=ais_customer.kodecustomer
					WHERE srt='1' AND ais_hutang.tglbkt<='$current_date' ";

                                        if ($office)   $SQL .= " AND ais_hutang.ktrasl = '".$office."' ";
                                        if ($curr)   $SQL .= " AND ais_hutang.curr <> '".$curr."' ";
                                        if ($debtur) $SQL .=" AND ais_customer.kodecustomer  = '".$debtur."' ";
                                        $SQL .= " order by nobkt";

                        //print $SQL;die();

			$DBConnection->dbc->query($SQL);

			$SQL = "DROP TABLE IF EXISTS `ais_hutang_terbayar`";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE `ais_hutang_terbayar` (
					  `nobkt` varchar(16) default NULL,					  
					  `nobkta` varchar(16) default NULL,
                                          `bayar` varchar(32) default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					INSERT INTO ais_hutang_terbayar
					SELECT NOBKT, NOBKTA, SUM(JUMLAH) 
					FROM ais_hutang
                                        LEFT JOIN ais_customer ON ais_hutang.DEBTUR=ais_customer.kodecustomer
					WHERE srt='2' AND ais_hutang.tglbkt<='$current_date' ";
                                        if ($office)   $SQL .= " AND ais_hutang.ktrasl = '".$office."' ";
                                        if ($curr)   $SQL .= " AND ais_hutang.curr <> '".$curr."' ";
                                        if ($debtur) $SQL .=" AND ais_customer.kodecustomer  = '".$debtur."' ";
                                        
                        $SQL .= " group by nobkta order by nobkta";
                        //print $SQL;die();
			$DBConnection->dbc->query($SQL);


			$SQL = "DROP TABLE IF EXISTS `ais_hutang_remain`";
			$DBConnection->dbc->query($SQL);

			$SQL = "
					CREATE $TEMP TABLE `ais_hutang_remain` (
					  `ktrasl` varchar(5) default NULL,
					  `tglbkt` date,
					  `nobkt` varchar(16) default NULL,
					  `debtur` varchar(16) default NULL,
					  `debnam` varchar(32) default NULL,
					  `uraian` text default NULL,
					  `curr` varchar(4) default NULL,
                                          `cr_term` tinyint(4)default NULL,
					  `jumlahcurr` varchar(32)default NULL,
					  `jumlah30` varchar(32)default NULL,
					  `jumlah60` varchar(32)default NULL,
					  `jumlah60g` varchar(32)default NULL
					) TYPE=MyISAM;
				   ";
			$DBConnection->dbc->query($SQL);


                        $SQL = "
                                        INSERT INTO ais_hutang_remain
                                        SELECT 
                                        ais_hutang_terhutang.ktrasl,
                                        ais_hutang_terhutang.tglbkt,
                                        ais_hutang_terhutang.nobkt,
                                        ais_hutang_terhutang.debtur,
                                        ais_hutang_terhutang.debnam,
                                        ais_hutang_terhutang.uraian,
                                        ais_hutang_terhutang.curr,
                                        ifnull(ais_hutang_terhutang.cr_term, 0) AS top,
                                        if(ais_hutang_terhutang.aging='0', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlahcurr,
                                        if(ais_hutang_terhutang.aging='30', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah30,
                                        if(ais_hutang_terhutang.aging='60', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah60,
                                        if(ais_hutang_terhutang.aging='60g', ais_hutang_terhutang.jumlah-ifnull(ais_hutang_terbayar.bayar,0) ,0) as jumlah90
                                        FROM
                                        ais_hutang_terhutang
                                        LEFT JOIN ais_hutang_terbayar ON (ais_hutang_terhutang.nobkt = ais_hutang_terbayar.nobkta)
                                        WHERE
                                          (ifnull(ais_hutang_terbayar.nobkt, '') = '') OR (ais_hutang_terhutang.jumlah-ais_hutang_terbayar.bayar) <> 0
                                        ORDER BY debtur,nobkt
                        ";
			$DBConnection->dbc->query($SQL);

}

?>
